package com.bookstore.back.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bookstore.back.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 订单Mapper接口
 *
 * @author 程序猿_Ti
 * @since 2025-07-19
 */
@Mapper
public interface OrderMapper extends BaseMapper<Order> {

    /**
     * 获取最近12个月的销售趋势数据
     */
    @Select("SELECT " +
            "  DATE_FORMAT(created_at, '%Y-%m') as month, " +
            "  CONCAT(MONTH(created_at), '月') as month_name, " +
            "  COUNT(*) as order_count, " +
            "  COALESCE(SUM(actual_amount), 0) as total_sales " +
            "FROM `order` " +
            "WHERE status >= 1 " +  // 只统计已支付的订单
            "  AND created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH) " +
            "GROUP BY DATE_FORMAT(created_at, '%Y-%m'), MONTH(created_at) " +
            "ORDER BY month ASC")
    List<Map<String, Object>> getSalesTrendData();

    /**
     * 获取分类销售统计数据
     */
    @Select("SELECT " +
            "  bc.name as category_name, " +
            "  COALESCE(SUM(oi.subtotal), 0) as total_sales " +
            "FROM book_category bc " +
            "LEFT JOIN book b ON bc.id = b.category_id " +
            "LEFT JOIN order_item oi ON b.id = oi.book_id " +
            "LEFT JOIN `order` o ON oi.order_id = o.id " +
            "WHERE bc.status = 1 " +  // 只统计启用的分类
            "  AND (o.status IS NULL OR o.status >= 1) " +  // 只统计已支付的订单
            "GROUP BY bc.id, bc.name " +
            "HAVING total_sales > 0 " +  // 只返回有销售的分类
            "ORDER BY total_sales DESC")
    List<Map<String, Object>> getCategorySalesData();

}
